﻿using System;
using System.Text;
using System.Data.SqlClient;
using System.Collections.Generic; 
using System.Data;
using System.Linq;
using Comm.Model;
using Comm.Utility;

namespace Comm.DAL
{
	public partial class BooksDAO
	{
        #region 向数据库中添加一条记录 +int Insert(Books model)
        /// <summary>
        /// 向数据库中添加一条记录
        /// </summary>
        /// <param name="model">要添加的实体</param>
        /// <returns>插入数据的ID</returns>
        public int Insert(Books model)
        {
            #region SQL语句
            const string sql = @"
INSERT INTO [dbo].[Books] (
	[Title]
	,[Author]
	,[PublisherId]
	,[PublishDate]
	,[ISBN]
	,[UnitPrice]
	,[ContentDescription]
	,[AurhorDescription]
	,[CategoryId]
)
VALUES (
	@Title
	,@Author
	,@PublisherId
	,@PublishDate
	,@ISBN
	,@UnitPrice
	,@ContentDescription
	,@AurhorDescription
	,@CategoryId
);select @@IDENTITY";
            #endregion
            var res = SqlHelper.ExecuteScalar(sql,
					new SqlParameter("@Title", model.Title),					
					new SqlParameter("@Author", model.Author),					
					new SqlParameter("@PublisherId", model.PublisherId),					
					new SqlParameter("@PublishDate", model.PublishDate),					
					new SqlParameter("@ISBN", model.ISBN),									
					new SqlParameter("@UnitPrice", model.UnitPrice),					
					new SqlParameter("@ContentDescription", model.ContentDescription),					
					new SqlParameter("@AurhorDescription", model.AurhorDescription),									
					new SqlParameter("@CategoryId", model.CategoryId)								
                );
            return res == null ? 0 : Convert.ToInt32(res);
        }
        #endregion

        #region 删除一条记录 +int Delete(int id)
        public int Delete(int id)
        {
            const string sql = "DELETE FROM [dbo].[Books] WHERE [Id] = @Id";
            return SqlHelper.ExecuteNonQuery(sql, new SqlParameter("@Id", id));
        }
        #endregion

        #region 根据主键ID更新一条记录 +int Update(Books model)
        /// <summary>
        /// 根据主键ID更新一条记录
        /// </summary>
        /// <param name="model">更新后的实体</param>
        /// <returns>执行结果受影响行数</returns>
        public int Update(Books model)
        {
            #region SQL语句
            const string sql = @"
UPDATE [dbo].[Books]
SET 
	[Title] = @Title
	,[Author] = @Author
	,[PublisherId] = @PublisherId
	,[PublishDate] = @PublishDate
	,[ISBN] = @ISBN
	,[UnitPrice] = @UnitPrice
	,[ContentDescription] = @ContentDescription
	,[AurhorDescription] = @AurhorDescription
	,[CategoryId] = @CategoryId
WHERE [Id] = @Id";
            #endregion
            return SqlHelper.ExecuteNonQuery(sql,
					new SqlParameter("@Id", model.Id),					
					new SqlParameter("@Title", model.Title),					
					new SqlParameter("@Author", model.Author),					
					new SqlParameter("@PublisherId", model.PublisherId),					
					new SqlParameter("@PublishDate", model.PublishDate),					
					new SqlParameter("@ISBN", model.ISBN),									
					new SqlParameter("@UnitPrice", model.UnitPrice),					
					new SqlParameter("@ContentDescription", model.ContentDescription),					
					new SqlParameter("@AurhorDescription", model.AurhorDescription),									
					new SqlParameter("@CategoryId", model.CategoryId)				
                );
        }
        #endregion

        #region 分页查询一个集合 +IEnumerable<Books> QueryList(int index, int size, object wheres, string orderField, bool isDesc = true)
        /// <summary>
        /// 分页查询一个集合
        /// </summary>
        /// <param name="index">页码</param>
        /// <param name="size">页大小</param>
        /// <param name="wheres">条件匿名类</param>
        /// <param name="orderField">排序字段</param>
        /// <param name="isDesc">是否降序排序</param>
        /// <returns>实体集合</returns>
        public IEnumerable<Books> QueryList(int index, int size, object wheres, string orderField, bool isDesc = true)
        {
            var parameters = new List<SqlParameter>();
            var whereBuilder = new System.Text.StringBuilder();
            if (wheres != null)
            {
                var props = wheres.GetType().GetProperties();
                foreach (var prop in props)
                {
                    if (prop.Name.Equals("__o", StringComparison.InvariantCultureIgnoreCase))
                    {
                        // 操作符
                        whereBuilder.AppendFormat(" {0} ", prop.GetValue(wheres, null).ToString());
                    }
                    else
                    {
                        var val = prop.GetValue(wheres, null).ToString();
                        whereBuilder.AppendFormat(" [{0}] = @{0} ", prop.Name);
                        parameters.Add(new SqlParameter("@" + prop.Name, val));
                    }
                }
            }
            var sql = SqlHelper.GenerateQuerySql("Books", new[] { "Id", "Title", "Author", "PublisherId", "PublishDate", "ISBN", "WordsCount", "UnitPrice", "ContentDescription", "AurhorDescription", "EditorComment", "TOC", "CategoryId", "Clicks" }, index, size, whereBuilder.ToString(), orderField, isDesc);
            using (var reader = SqlHelper.ExecuteReader(sql, parameters.ToArray()))
            {
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        yield return SqlHelper.MapEntity<Books>(reader);
                    }
                }
            }
        }
        #endregion

        #region 查询单个模型实体 +Books QuerySingle(object wheres)
        /// <summary>
        /// 查询单个模型实体
        /// </summary>
        /// <param name="wheres">条件匿名类</param>
        /// <returns>实体</returns>
        public Books QuerySingle(object wheres)
        {
            var list = QueryList(1, 1, wheres, null);
            return list != null && list.Any() ? list.FirstOrDefault() : null;
        }
        #endregion

        #region 查询单个模型实体 +Books QuerySingle(int id)
        /// <summary>
        /// 查询单个模型实体
        /// </summary>
        /// <param name="id">key</param>
        /// <returns>实体</returns>
        public Books QuerySingle(int id)
        {
            const string sql = "SELECT TOP 1 [Id], [Title], [Author], [PublisherId], [PublishDate], [ISBN], [WordsCount], [UnitPrice], [ContentDescription], [AurhorDescription], [EditorComment], [TOC], [CategoryId], [Clicks] FROM [dbo].[Books] WHERE [Id] = @Id";
            using (var reader = SqlHelper.ExecuteReader(sql, new SqlParameter("@Id", id)))
            {
                if (reader.HasRows)
                {
                    reader.Read();
                    return SqlHelper.MapEntity<Books>(reader);
                }
                else
                {
                    return null;
                }
            }
        }
        #endregion

        #region 查询条数 +int QueryCount(object wheres)
        /// <summary>
        /// 查询条数
        /// </summary>
        /// <param name="wheres">查询条件</param>
        /// <returns>条数</returns>
        public int QueryCount(object wheres)
        {
            var parameters = new List<SqlParameter>();
            var whereBuilder = new System.Text.StringBuilder();
            if (wheres != null)
            {
                var props = wheres.GetType().GetProperties();
                foreach (var prop in props)
                {
                    if (prop.Name.Equals("__o", StringComparison.InvariantCultureIgnoreCase))
                    {
                        // 操作符
                        whereBuilder.AppendFormat(" {0} ", prop.GetValue(wheres, null).ToString());
                    }
                    else
                    {
                        var val = prop.GetValue(wheres, null).ToString();
                        whereBuilder.AppendFormat(" [{0}] = @{0} ", prop.Name);
                        parameters.Add(new SqlParameter("@" + prop.Name, val));
                    }
                }
            }
            var sql = SqlHelper.GenerateQuerySql("Books", new[] { "COUNT(1)" }, whereBuilder.ToString(), string.Empty);
            var res = SqlHelper.ExecuteScalar(sql, parameters.ToArray());
            return res == null ? 0 : Convert.ToInt32(res);
        }
        #endregion
		
				/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public Books GetModel(int Id)
		{
			
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select Id, Title, Author, PublisherId, PublishDate, ISBN, WordsCount, UnitPrice, ContentDescription, AurhorDescription, EditorComment, TOC, CategoryId, Clicks  ");			
			strSql.Append("  from Books ");
			strSql.Append(" where Id=@Id");
						SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
			};
			parameters[0].Value = Id;

			
			Books model=new Books();
			DataSet ds=SqlHelper.Query(strSql.ToString(),parameters);
			
			if(ds.Tables[0].Rows.Count>0)
			{
                Books book = new Books();
				 if(ds.Tables[0].Rows[0]["Id"].ToString()!="")
				{
                    book.Id = int.Parse(ds.Tables[0].Rows[0]["Id"].ToString());
				}
                 book.Title = ds.Tables[0].Rows[0]["Title"].ToString();
                 book.Author = ds.Tables[0].Rows[0]["Author"].ToString();
			if(ds.Tables[0].Rows[0]["PublisherId"].ToString()!="")
				{
                    book.PublisherId = int.Parse(ds.Tables[0].Rows[0]["PublisherId"].ToString());
				}
				if(ds.Tables[0].Rows[0]["PublishDate"].ToString()!="")
				{
                    book.PublishDate = DateTime.Parse(ds.Tables[0].Rows[0]["PublishDate"].ToString());
				}
                book.ISBN = ds.Tables[0].Rows[0]["ISBN"].ToString();
			if(ds.Tables[0].Rows[0]["WordsCount"].ToString()!="")
				{
                    book.WordsCount = int.Parse(ds.Tables[0].Rows[0]["WordsCount"].ToString());
				}
				if(ds.Tables[0].Rows[0]["UnitPrice"].ToString()!="")
				{
                    book.UnitPrice = decimal.Parse(ds.Tables[0].Rows[0]["UnitPrice"].ToString());
				}
                book.ContentDescription = ds.Tables[0].Rows[0]["ContentDescription"].ToString();
                book.AurhorDescription = ds.Tables[0].Rows[0]["AurhorDescription"].ToString();
                book.EditorComment = ds.Tables[0].Rows[0]["EditorComment"].ToString();
                book.TOC = ds.Tables[0].Rows[0]["TOC"].ToString();
                 if (ds.Tables[0].Rows[0]["CategoryId"].ToString() != "")
				{
                    book.CategoryId = int.Parse(ds.Tables[0].Rows[0]["CategoryId"].ToString());
				}
			if(ds.Tables[0].Rows[0]["Clicks"].ToString()!="")
				{
                    book.Clicks = int.Parse(ds.Tables[0].Rows[0]["Clicks"].ToString());
				}
            model.BooksList.Add(book);																									
				return model;
			}
			else
			{
				return null;
			}
		}


        public void GetModel1(Books model, DataRow dr)
        {
                if (dr["Id"].ToString() != "")
                {
                    model.Id = int.Parse(dr["Id"].ToString());
                }
                model.Title = dr["Title"].ToString();
                model.Author = dr["Author"].ToString();
                if (dr["PublisherId"].ToString() != "")
                {
                    model.PublisherId = int.Parse(dr["PublisherId"].ToString());
                }
                if (dr["PublishDate"].ToString() != "")
                {
                    model.PublishDate = DateTime.Parse(dr["PublishDate"].ToString());
                }
                model.ISBN = dr["ISBN"].ToString();
                if (dr["WordsCount"].ToString() != "")
                {
                    model.WordsCount = int.Parse(dr["WordsCount"].ToString());
                }
                if (dr["UnitPrice"].ToString() != "")
                {
                    model.UnitPrice = decimal.Parse(dr["UnitPrice"].ToString());
                }
                model.ContentDescription = dr["ContentDescription"].ToString();
                model.AurhorDescription = dr["AurhorDescription"].ToString();
                model.EditorComment = dr["EditorComment"].ToString();
                model.TOC = dr["TOC"].ToString();
                if (dr["CategoryId"].ToString() != "")
                {
                    model.CategoryId = int.Parse(dr["CategoryId"].ToString());
                }
                if (dr["Clicks"].ToString() != "")
                {
                    model.Clicks = int.Parse(dr["Clicks"].ToString());
                }
        }


		/// <summary>
		/// 获得数据列表
		/// </summary>
		public DataSet GetList(string strWhere)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select * ");
			strSql.Append(" FROM Books ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			return SqlHelper.Query(strSql.ToString());
		}
		
		/// <summary>
		/// 获得前几行数据
		/// </summary>
		public DataSet GetList(int Top,string strWhere,string filedOrder)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select ");
			if(Top>0)
			{
				strSql.Append(" top "+Top.ToString());
			}
			strSql.Append(" * ");
			strSql.Append(" FROM Books ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			strSql.Append(" order by " + filedOrder);
			return SqlHelper.Query(strSql.ToString());
		}


        /// <summary>
        /// 根据指定的图书的类别取出指定类别下的总的记录数
        /// </summary>
        /// <param name="categoryId">如果类别编号为0，表示查看所有的类别</param>
        /// <returns></returns>
        public int GetRecordCount(int categoryId)
        {
            string sql = "select count(*) from Books";
            if (categoryId != 0)
            {
                sql = sql + " where CategoryId=@CategoryId";
            }
            return Convert.ToInt32(SqlHelper.GetSingle(sql, new SqlParameter("@CategoryId", categoryId)));
        }

        public int GetRecordCount1(int categoryId,string Bname)
        {
            string sql = "select count(*) from Books";
            if (categoryId != 0)
            {
                sql = sql + " where CategoryId=@CategoryId and Title LIKE '%" + Bname + "%'";
            }else{
              sql += " where Title LIKE '%" + Bname + "%'";
            }
            return Convert.ToInt32(SqlHelper.GetSingle(sql, new SqlParameter("@CategoryId", categoryId)));
        }
        /// <summary>
        /// 获取分页的数据
        /// </summary>
        /// <returns></returns>
        public IList<Model.Books> GetPageData(int start, int end, int categoryId)
        {
            return GetPageData(start, end, categoryId, "","");
        }
        //获取排序的数据
        public  IList<Model.Books> GetPageData(int start, int end, int categoryId, string orderby,string search)
        {
            string sql = "select * from(select row_number()over(order by {1})as num,* from Books {0} {2}) as t where num>=@start and num<=@end ";
            if (categoryId != 0)
            {
                sql = string.Format(sql, categoryId != 0 ? " where CategoryId=@CategoryId" : "", (orderby != null && orderby != "") ? orderby : "id", (search != null && search != "") ? " and Title like '%" + search + "%'" : "");
            }
            else {
                sql = string.Format(sql, categoryId != 0 ? " where CategoryId=@CategoryId" : "", (orderby != null && orderby != "") ? orderby : "id", (search != null && search != "") ? " where Title like '%" + search + "%'" : "");
            }
            SqlParameter[] par = { 
                                  new SqlParameter("@start",start),
                                  new SqlParameter("@end",end),
                                  new SqlParameter("@CategoryId",categoryId)
                                 };

            DataTable dt = SqlHelper.ExecuteDataTable(sql,par);
            IList<Model.Books> list = null;
            if (dt.Rows.Count > 0) 
            {
                list = common.TBToList< Model.Books>.Table2List(dt);
            }
            return list;

        }

        public IList<Books> GetBooksList() 
        {
            string sql = "select * from Books";
            DataTable dt = SqlHelper.ExecuteDataTable(sql);
            IList<Books> lis = null;
            if (dt.Rows.Count > 0) 
            {
                lis = common.TBToList<Books>.Table2List(dt);
            }
            return lis;
        }
	}
}